BigQueryのCREATE TABLE LIKEステートメントで、同じメタデータのテーブルにふたつのテーブルをまとめてみる

#Google Cloud (GCP)

#Google BigQuery

nayu.t.s

2023.03.13

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

データアナリティクス事業本部の鈴木です。

BigQueryのCREATE TABLE LIKEステートメントで、もとのテーブルと同じメタデータのテーブルに、ふたつのテーブルのデータをまとめる検証をしてみました。

CREATE TABLE LIKEステートメントについて

CREATE TABLE LIKEステートメントについては、記事執筆時点で、以下のドキュメントに別のテーブルの同じメタデータをすべて含む新しいテーブルを作成します。の記載があります。

単純にCTASするとメタデータは新しいテーブルに反映されませんが、CREATE TABLE LIKEステートメントとas query_statement句を併せて使用することで、メタデータはもとのテーブルと同じものを設定しつつ、そのテーブルにデータを追加することができます。

このステートメントは、CREATE TABLE ステートメントのバリアントであり、同じ制限事項があります。列リストの代わりに LIKE 句を使用する点を除き、構文は CREATE TABLE 構文と同じです。

CREATE TABLE LIKE ステートメントは、ソーステーブルのメタデータのみをコピーします。as query_statement 句を使用することで、新しいテーブルにデータを追加できます。

※上記ドキュメントより2023/03/13に引用しました。

データの準備

以前別のブログで使った、簡単なフルーツの価格のデータを持った2テーブルをまとめます。

-- もとのテーブル
CREATE TABLE sample_dataset.original_table
(
    id INTEGER OPTIONS(description="商品ID"),
    name STRING OPTIONS(description="商品名"),
    price INTEGER OPTIONS(description="商品価格")
);

INSERT sample_dataset.original_table (id, name, price)
VALUES(1, 'Apple', 150),
      (2, 'Orange', 120),
      (3, 'Grape', 390);

-- 追加したいデータの入ったテーブル
CREATE TABLE sample_dataset.additional_data_table
(
    id INTEGER OPTIONS(description="商品ID"),
    name STRING OPTIONS(description="商品名"),
    price INTEGER OPTIONS(description="商品価格")
);

INSERT sample_dataset.additional_data_table (id, name, price)
VALUES(3, 'Grape', 390),
      (4, 'Pineapple', 400),
      (5, 'Peach', 350);

今回は意地悪なケースで、追加したいデータの入ったテーブルには、もとのテーブルに入っているのと同じデータが1件入っているので、忘れず重複の排除をしたいところです。

SQLを実行して、以下のようにテーブルを作成しました。

作成したテーブル

メタデータとしては以下のように説明がついています。

コメントの確認

方針

まずALTER文で大元のテーブルのテーブル名を変更します。

その後、CREATE TABLE LIKEステートメントで名前を変更したもとのテーブルのメタデータを引き継いだ新しいテーブルを作成します。データはas query_statement句を使って、2テーブルのデータを合わせたものを追加します。このとき、念の為テーブル間のデータ重複がないよう、UNION DISTINCTでデータを結合します。

やってみた

同じデータセット内でテーブルをまとめる

BigQueryのコンソールから操作しました。

まず、クエリエディタから以下のSQL文を入力し、実行を押しました。

-- もとのテーブルの名前を変更する
ALTER TABLE sample_dataset.original_table RENAME TO original_table_bk;

リロードすると以下のようにテーブル名が変わったことを確認できました。

もとのテーブルの名前の変更

次に、以下のSQL文を実行して、もとのテーブルと同じメタデータのテーブルを作成し、さらに2テーブルのデータを格納しました。

-- 名前変更後のもとのテーブルと同じメタデータで、
-- 2テーブルを合わせたデータを持った新しいテーブルを作成する
CREATE TABLE sample_dataset.original_table
LIKE sample_dataset.original_table_bk
AS 
  SELECT 
    id,
    name,
    price
  FROM sample_dataset.original_table_bk
  UNION DISTINCT
  SELECT 
    id,
    name,
    price
  FROM sample_dataset.additional_data_table

以下のように新しいテーブルができました。

新しいテーブル

スキーマおよびメタデータがもとのテーブルと同じですね。

スキーマとメタデータ

データも2テーブル分入っていることが確認できます。重複も解消されています。

格納されたデータ

最後にもとのテーブルを削除しておきます。

-- 名前変更後のもとのテーブルを削除する
DROP TABLE sample_dataset.original_table_bk

これでデータセット配下にあるテーブルの名前は元通りになりましたね。

もとのテーブルの削除

同じロケーションの異なるデータセット内でテーブルをまとめる

気になったのでデータセットを跨いだCREATE TABLE LIKEステートメントも試してみました。

additional_data_tableはsample_dataset2データセットに作ります。ただし、データセットのロケーションは両方asia-northeast1です。

異なるデータセットの場合

以下のSQL文を順番に実行しました。(結果が分かりやすいよう、最後のもとのテーブルの削除は省きました。)

-- もとのテーブルの名前を変更する
ALTER TABLE sample_dataset.original_table RENAME TO original_table_bk;

-- 名前変更後のもとのテーブルと同じメタデータで、
-- 2テーブルを合わせたデータを持った新しいテーブルを作成する
CREATE TABLE sample_dataset.original_table
LIKE sample_dataset.original_table_bk
AS 
  SELECT 
    id,
    name,
    price
  FROM sample_dataset.original_table_bk
  UNION DISTINCT
  SELECT 
    id,
    name,
    price
  FROM sample_dataset2.additional_data_table

以下のように、データセットが同じときと同様に処理が実行できました。

処理実行後のデータセット